package cn.javacart.jopencart.service.catalog.catalog;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.javacart.jopencart.library.SessionConfigService;
import cn.javacart.jopencart.model.AttributeGroup;
import cn.javacart.jopencart.model.Product;
import cn.javacart.jopencart.model.ProductAttribute;
import cn.javacart.jopencart.model.ProductDiscount;
import cn.javacart.jopencart.model.ProductImage;
import cn.javacart.jopencart.model.ProductOption;
import cn.javacart.jopencart.model.ProductOptionValue;
import cn.javacart.jopencart.model.ProductRecurring;
import cn.javacart.jopencart.model.ProductRelated;
import cn.javacart.jopencart.util.ChainMap;

import com.jfinal.aop.Duang;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.SqlPara;

/**
 * 商品服务
 * @author farmer
 *
 */
public class CatalogProductService {

	public final static CatalogProductService ME = Duang.duang(CatalogProductService.class);
	
	/**
	 * 根据商品ID获取商品信息
	 * @param productId
	 */
	public Product getProduct(Integer productId){	
		String sql = "SELECT\n" + 
				"	DISTINCT *,\n" + 
				"	pd.name AS name,\n" + 
				"	p.image,\n" + 
				"	m.name AS manufacturer,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			price\n" + 
				"		FROM\n" + 
				"			joc_product_discount pd2\n" + 
				"		WHERE\n" + 
				"			pd2.product_id = p.product_id\n" + 
				"			AND pd2.customer_group_id = '1'\n" + 
				"			AND pd2.quantity = '1'\n" + 
				"			AND(\n" + 
				"				(\n" + 
				"					pd2.date_start = '0000-00-00'\n" + 
				"					OR pd2.date_start < NOW()\n" + 
				"				)\n" + 
				"				AND(\n" + 
				"					pd2.date_end = '0000-00-00'\n" + 
				"					OR pd2.date_end > NOW()\n" + 
				"				)\n" + 
				"			)\n" + 
				"		ORDER BY\n" + 
				"			pd2.priority ASC,\n" + 
				"			pd2.price ASC LIMIT 1\n" + 
				"	) AS discount,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			price\n" + 
				"		FROM\n" + 
				"			joc_product_special ps\n" + 
				"		WHERE\n" + 
				"			ps.product_id = p.product_id\n" + 
				"			AND ps.customer_group_id = '1'\n" + 
				"			AND(\n" + 
				"				(\n" + 
				"					ps.date_start = '0000-00-00'\n" + 
				"					OR ps.date_start < NOW()\n" + 
				"				)\n" + 
				"				AND(\n" + 
				"					ps.date_end = '0000-00-00'\n" + 
				"					OR ps.date_end > NOW()\n" + 
				"				)\n" + 
				"			)\n" + 
				"		ORDER BY\n" + 
				"			ps.priority ASC,\n" + 
				"			ps.price ASC LIMIT 1\n" + 
				"	) AS special,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			points\n" + 
				"		FROM\n" + 
				"			joc_product_reward pr\n" + 
				"		WHERE\n" + 
				"			pr.product_id = p.product_id\n" + 
				"			AND customer_group_id = '1'\n" + 
				"	) AS reward,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			ss.name\n" + 
				"		FROM\n" + 
				"			joc_stock_status ss\n" + 
				"		WHERE\n" + 
				"			ss.stock_status_id = p.stock_status_id\n" + 
				"			AND ss.language_id = ?\n" + 
				"	) AS stock_status,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			wcd.unit\n" + 
				"		FROM\n" + 
				"			joc_weight_class_description wcd\n" + 
				"		WHERE\n" + 
				"			p.weight_class_id = wcd.weight_class_id\n" + 
				"			AND wcd.language_id = ?\n" + 
				"	) AS weight_class,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			lcd.unit\n" + 
				"		FROM\n" + 
				"			joc_length_class_description lcd\n" + 
				"		WHERE\n" + 
				"			p.length_class_id = lcd.length_class_id\n" + 
				"			AND lcd.language_id = ?\n" + 
				"	) AS length_class,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			AVG( rating ) AS total\n" + 
				"		FROM\n" + 
				"			joc_review r1\n" + 
				"		WHERE\n" + 
				"			r1.product_id = p.product_id\n" + 
				"			AND r1.status = '1'\n" + 
				"		GROUP BY\n" + 
				"			r1.product_id\n" + 
				"	) AS rating,\n" + 
				"	(\n" + 
				"		SELECT\n" + 
				"			COUNT(*) AS total\n" + 
				"		FROM\n" + 
				"			joc_review r2\n" + 
				"		WHERE\n" + 
				"			r2.product_id = p.product_id\n" + 
				"			AND r2.status = '1'\n" + 
				"		GROUP BY\n" + 
				"			r2.product_id\n" + 
				"	) AS reviews,\n" + 
				"	p.sort_order\n" + 
				"FROM\n" + 
				"	joc_product p\n" + 
				"LEFT JOIN joc_product_description pd ON\n" + 
				"	(\n" + 
				"		p.product_id = pd.product_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_product_to_store p2s ON\n" + 
				"	(\n" + 
				"		p.product_id = p2s.product_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_manufacturer m ON\n" + 
				"	(\n" + 
				"		p.manufacturer_id = m.manufacturer_id\n" + 
				"	)\n" + 
				"WHERE\n" + 
				"	p.product_id = ?\n" + 
				"	AND pd.language_id = ?\n" + 
				"	AND p.status = '1'\n" + 
				"	AND p.date_available <= NOW()\n" + 
				"	AND p2s.store_id = '0'";
		Product product = Product.ME.findFirst(sql, 
				SessionConfigService.get("config_language_id"),SessionConfigService.get("config_language_id"),
				SessionConfigService.get("config_language_id"),productId,SessionConfigService.get("config_language_id"));
		if(product != null){
			product.set("price", product.get("discount") != null ? product.get("discount") : product.get("price"));
			product.put("reviews", product.get("reviews") != null ? product.get("reviews") : 0);
			product.put("rating", Math.round( 
					(product.getDouble("total") != null) 
					? product.getDouble("total") : 0d));
		}
		return product;
	}

	/**
	 * 获取商品图片
	 * @param productId
	 * @return
	 */
	public List<ProductImage> getProductImages(Integer productId) {
		return ProductImage.ME.find("select * from joc_product_image t where t.product_id = ? ORDER BY sort_order ASC",productId);
	}

	/**
	 * 获取商品折扣
	 * @param productId
	 * @return
	 */
	public List<ProductDiscount> getProductDiscounts(Integer productId) {
		return ProductDiscount.ME.find("SELECT\n" + 
				"	*\n" + 
				"FROM\n" + 
				"	joc_product_discount\n" + 
				"WHERE\n" + 
				"	product_id = ?\n" + 
				"	AND customer_group_id = '1'\n" + 
				"	AND quantity > 1\n" + 
				"	AND(\n" + 
				"		(\n" + 
				"			date_start = '0000-00-00'\n" + 
				"			OR date_start < NOW()\n" + 
				"		)\n" + 
				"		AND(\n" + 
				"			date_end = '0000-00-00'\n" + 
				"			OR date_end > NOW()\n" + 
				"		)\n" + 
				"	)\n" + 
				"ORDER BY\n" + 
				"	quantity ASC,\n" + 
				"	priority ASC,\n" + 
				"	price ASC",productId);	
	}
	
	
	/**
	 * 获取商品配置信息
	 * @param productId
	 * @return
	 */
	public List<ProductOption> getProductOptions(Integer productId) {
		List<ProductOption> productOptionData = new ArrayList<ProductOption>();
		String sql = "SELECT\n" + 
				"	*\n" + 
				"FROM\n" + 
				"	joc_product_option po\n" + 
				"LEFT JOIN `joc_option` o ON\n" + 
				"	(\n" + 
				"		po.option_id = o.option_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_option_description od ON\n" + 
				"	(\n" + 
				"		o.option_id = od.option_id\n" + 
				"	)\n" + 
				"WHERE\n" + 
				"	po.product_id = ?\n" + 
				"	AND od.language_id = ?\n" + 
				"ORDER BY\n" + 
				"	o.sort_order";
		for (ProductOption productOption : ProductOption.ME.find(sql,productId,SessionConfigService.get("config_language_id"))) {
			sql = "SELECT\n" + 
					"	*\n" + 
					"FROM\n" + 
					"	joc_product_option_value pov\n" + 
					"LEFT JOIN joc_option_value ov ON\n" + 
					"	(\n" + 
					"		pov.option_value_id = ov.option_value_id\n" + 
					"	)\n" + 
					"LEFT JOIN joc_option_value_description ovd ON\n" + 
					"	(\n" + 
					"		ov.option_value_id = ovd.option_value_id\n" + 
					"	)\n" + 
					"WHERE\n" + 
					"	pov.product_id = ?\n" + 
					"	AND pov.product_option_id = ?\n" + 
					"	AND ovd.language_id = ?\n" + 
					"ORDER BY\n" + 
					"	ov.sort_order";
			List<ProductOptionValue> productOptionValueData = ProductOptionValue.ME.find(sql,productId,productOption.get("product_option_id"),SessionConfigService.get("config_language_id"));
			productOption.put("product_option_value", productOptionValueData);
			productOptionData.add(productOption);
		}
		return productOptionData;
	}
	
	/**
	 * 获取商品属性
	 * @param productId
	 * @return
	 */
	public List<Map<String, Object>> getProductAttributes(Integer productId){
		List<Map<String, Object>> productAttributeGroupData = new ArrayList<Map<String,Object>>();
		String sql = "SELECT\n" + 
				"	ag.attribute_group_id,\n" + 
				"	agd.name\n" + 
				"FROM\n" + 
				"	joc_product_attribute pa\n" + 
				"LEFT JOIN joc_attribute a ON\n" + 
				"	(\n" + 
				"		pa.attribute_id = a.attribute_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_attribute_group ag ON\n" + 
				"	(\n" + 
				"		a.attribute_group_id = ag.attribute_group_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_attribute_group_description agd ON\n" + 
				"	(\n" + 
				"		ag.attribute_group_id = agd.attribute_group_id\n" + 
				"	)\n" + 
				"WHERE\n" + 
				"	pa.product_id = ?\n" + 
				"	AND agd.language_id = ?\n" + 
				"GROUP BY\n" + 
				"	ag.attribute_group_id\n" + 
				"ORDER BY\n" + 
				"	ag.sort_order,\n" + 
				"	agd.name";
		for (AttributeGroup attributeGroup : AttributeGroup.ME.find(sql,
				productId,SessionConfigService.get("config_language_id"))) {
			List<Map<String, Object>> productAttributeData = new ArrayList<Map<String,Object>>();
			sql = "SELECT\n" + 
					"	a.attribute_id,\n" + 
					"	ad.name,\n" + 
					"	pa.text\n" + 
					"FROM\n" + 
					"	joc_product_attribute pa\n" + 
					"LEFT JOIN joc_attribute a ON\n" + 
					"	(\n" + 
					"		pa.attribute_id = a.attribute_id\n" + 
					"	)\n" + 
					"LEFT JOIN joc_attribute_description ad ON\n" + 
					"	(\n" + 
					"		a.attribute_id = ad.attribute_id\n" + 
					"	)\n" + 
					"WHERE\n" + 
					"	pa.product_id = ?\n" + 
					"	AND a.attribute_group_id = ?\n" + 
					"	AND ad.language_id = ?\n" + 
					"	AND pa.language_id = ?\n" + 
					"ORDER BY\n" + 
					"	a.sort_order,\n" + 
					"	ad.name";
			for (ProductAttribute productAttribute : ProductAttribute.ME.find(sql,productId,attributeGroup.get("attribute_group_id"),SessionConfigService.get("config_language_id"),SessionConfigService.get("config_language_id"))) {
				productAttributeData.add(
						ChainMap.createMap()
						.put("attribute_id", productAttribute.get("attribute_id"))
						.put("name", productAttribute.get("name"))
						.put("text", productAttribute.get("text"))
						.toMap()
						);
			}
			productAttributeGroupData.add(
					ChainMap.createMap()
					.put("attribute_group_id", attributeGroup.get("attribute_group_id"))
					.put("name", attributeGroup.get("name"))
					.put("attribute",productAttributeData)
					.toMap()
					);
		}
		return productAttributeGroupData;
	}

	/**
	 * 获取相关商品
	 * @param productId
	 * @return
	 */
	public Map<Integer, Product> getProductRelated(Integer productId) {
		Map<Integer, Product> productData = new HashMap<Integer, Product>();
		String sql = "SELECT\n" + 
				"	*\n" + 
				"FROM\n" + 
				"	joc_product_related pr\n" + 
				"LEFT JOIN joc_product p ON\n" + 
				"	(\n" + 
				"		pr.related_id = p.product_id\n" + 
				"	)\n" + 
				"LEFT JOIN joc_product_to_store p2s ON\n" + 
				"	(\n" + 
				"		p.product_id = p2s.product_id\n" + 
				"	)\n" + 
				"WHERE\n" + 
				"	pr.product_id = ?\n" + 
				"	AND p.status = '1'\n" + 
				"	AND p.date_available <= NOW()\n" + 
				"	AND p2s.store_id = '0'";
		for (ProductRelated productRelated : ProductRelated.ME.find(sql,productId)) {
			productData.put(productRelated.getInt("related_id"), this.getProduct(productRelated.getInt("related_id")));
		}
		return productData;
	}

	
	/**
	 * 获取简介
	 * @param productId
	 * @return
	 */
	public List<Map<String, Object>> getProfiles(Integer productId) {
		List<Map<String, Object>> productRecurringData = new ArrayList<Map<String,Object>>();
		String sql = "SELECT\n" + 
				"	`pd` .*\n" + 
				"FROM\n" + 
				"	`joc_product_recurring` `pp`\n" + 
				"JOIN `joc_recurring_description` `pd` ON\n" + 
				"	`pd`.`language_id` = 1\n" + 
				"	AND `pd`.`recurring_id` = `pp`.`recurring_id`\n" + 
				"JOIN `joc_recurring` `p` ON\n" + 
				"	`p`.`recurring_id` = `pd`.`recurring_id`\n" + 
				"WHERE\n" + 
				"	`product_id` = ?\n" + 
				"	AND `status` = 1\n" + 
				"	AND `customer_group_id` = 1\n" + 
				"ORDER BY\n" + 
				"	`sort_order` ASC";
		for (ProductRecurring productRecurring : ProductRecurring.ME.find(sql, productId)) {
			productRecurringData.add(productRecurring.toRecord().getColumns());
		}
		return productRecurringData;
	}

	/**
	 * 更新浏览次数
	 * @param productId
	 * @return
	 */
	public boolean updateViewed(Integer productId) {
		Product product = Product.ME.findById(productId);
		return product.set("viewed", product.getInt("viewed")!=null? product.getInt("viewed")+1 : 1 ).update();
	}

	/**
	 * 根据过滤条件获取商品总数
	 * @param filterMap
	 * @return
	 */
	public Long getTotalProducts(Map<String, Object> filterMap) {
		filterMap.put("config_language_id", SessionConfigService.get("config_language_id"));
		SqlPara sqlPara = Db.getSqlPara("catalog.catalog.product.getTotalProducts", filterMap);
		return Db.queryLong(sqlPara.getSql(),sqlPara.getPara());
	}

	/**
	 * 获取商品
	 * @param filterMap
	 * @return
	 */
	public  Map<Integer, Product> getProducts(Map<String, Object> filterMap) {
		Map<Integer, Product> productData = new HashMap<Integer, Product>();
		filterMap.put("config_language_id", SessionConfigService.get("config_language_id"));
		SqlPara sqlPara = Db.getSqlPara("catalog.catalog.product.getProducts", filterMap);
		List<Product> products = Product.ME.find(sqlPara.getSql(), sqlPara.getPara());
		for (Product product : products) {
			productData.put(product.getInt("product_id"), this.getProduct(product.getInt("product_id")));
		}
		return productData;
	}
	
}
